<?php
function entityExp(){
	global $objSDAO;
	$errMsg = "";
	$aExpEnt = array();
	
	foreach($_REQUEST as $key=>$value){
		if(strpos($key, "exp_") !== false){
			$aExpEnt[] = $value;
		}
	}
	if(count($aExpEnt) == 0){
		$errMsg .= _t("No entity selected.");
	}else{
		$errMsg .= "entities ".implode(", ", $aExpEnt)." - export running ...<br>";
		$expFile = SERVER_FS_ROOT."/entity_export.txt";
		$fh = fopen($expFile, "wb");
		$aExportEntity = array(); 
		foreach($aExpEnt as $entity){
			//recupero le info sulla entity
			$sSQL = "SELECT * FROM entity 
		 				WHERE entity='$entity'";
			$objSDAO->query($sSQL,"selEnt");
			while($row = $objSDAO->fetch("selEnt")){
				$aExportEntity[$row["entity"]] = array("data_area"=>$row["data_area"], 
								"description"=>$row["description"],
								"note"=>$row["note"],
								"physical_param"=>$row["physical_param"]
								);
				//recupero le info sulle SA a cui la entity appartiene		
				$sSQL = "SELECT * FROM subject_area_entity sae
							INNER JOIN subject_area sa ON sa.data_area=sae.data_area AND sa.subject_area=sae.subject_area
						  WHERE entity='$entity'";
				$objSDAO->query($sSQL,"selSA");
				$numSA = 0;
				while($rowSA = $objSDAO->fetch("selSA")){
					$numSA ++;
					$aExportEntity[$row["entity"]]["subject_area"][$numSA] = array(
						"data_area"=>$rowSA["data_area"], 
						"subject_area"=>$rowSA["subject_area"], 
						"entity"=>$rowSA["entity"], 
						"description"=>$rowSA["description"], 
						"note"=>$rowSA["note"]
					);
				}		
				//recupero le info sugli attributi della entity
				$sSQL = "SELECT * FROM attribute WHERE entity='$entity'";
				$objSDAO->query($sSQL,"selAttr");
				$numAttr = 0;
				$numDom = 0;
				$numRel = 0;
				while($rowAttr = $objSDAO->fetch("selAttr")){
					$numAttr ++;
					$aExportEntity[$row["entity"]]["attribute"][$numAttr] = array(
						"entity"=>$rowAttr["entity"], 
						"attribute"=>$rowAttr["attribute"], 
						"sequence"=>$rowAttr["sequence"], 
						"domain"=>$rowAttr["domain"], 
						"upgrade_status"=>$rowAttr["upgrade_status"], 
						"length"=>$rowAttr["length"], 
						"decimals"=>$rowAttr["decimals"], 
						"default_value"=>$rowAttr["default_value"], 
						"description"=>$rowAttr["description"], 
						"note"=>$rowAttr["note"], 
						"primary_key"=>$rowAttr["primary_key"], 
						"indexed"=>$rowAttr["indexed"], 
						"physical_param"=>$rowAttr["physical_param"]
					);
					//recupero le info sui domini
					$sSQL = "SELECT d.domain, d.lookup_entity, d.lookup_attribute, d.upgrade_status, d.type, 
								d.length, d.decimals, d.enumerable, d.description AS descr_dom, 
								d.note AS note_dom, dv.value, dv.description as descr_value, 
								dv.note as note_value FROM domain d
								LEFT OUTER JOIN domain_value dv ON dv.domain=d.domain 
								WHERE d.domain='".$rowAttr["domain"]."'";
					$objSDAO->query($sSQL,"selDom");
					while($rowDom = $objSDAO->fetch("selDom")){
						$numDom ++;
						$aExportEntity[$row["entity"]]["domain"][$numDom] = array(
							"domain"=>$rowDom["domain"], 
							"lookup_entity"=>$rowDom["lookup_entity"],  
							"lookup_attribute"=>$rowDom["lookup_attribute"],  
							"upgrade_status"=>$rowDom["upgrade_status"],  
							"type"=>$rowDom["type"],  
							"length"=>$rowDom["length"],  
							"decimals"=>$rowDom["decimals"],  
							"enumerable"=>$rowDom["enumerable"],  
							"descr_dom"=>$rowDom["descr_dom"],  
							"note_dom"=>$rowDom["note_dom"],  
							"descr_value"=>$rowDom["descr_value"],  
							"note_value"=>$rowDom["note_value"]
						);
					}
				}
				//retrieve info about parent-entities
				$sSQL = "SELECT r.*, rk.parent_attribute, rk.child_attribute FROM relationship r " .
						"LEFT OUTER JOIN relationship_key rk " .
						"ON rk.parent_entity=r.parent_entity " .
						"AND rk.child_entity=r.child_entity " .
						"AND rk.number=r.number " .
						"WHERE r.parent_entity='$entity'";
				$objSDAO->query($sSQL,"selRel");
				while($rowRel = $objSDAO->fetch("selRel")){
					$numRel ++;
					$aExportEntity[$row["entity"]]["relationship"][$numRel] = array(
					"parent_entity"=>$rowRel["parent_entity"], 
					"child_entity"=>$rowRel["child_entity"], 
					"number"=>$rowRel["number"], 
					"description_parent_child"=>$rowRel["description_parent_child"], 
					"description_child_parent"=>$rowRel["description_child_parent"], 
					"note"=>$rowRel["note"], 
					"parent_optionality"=>$rowRel["parent_optionality"], 
					"children_optionality"=>$rowRel["children_optionality"], 
					"children_multeplicity"=>$rowRel["children_multeplicity"], 
					"index_creation"=>$rowRel["index_creation"], 
					"integrity_check"=>$rowRel["integrity_check"],
					"parent_attribute"=>$rowRel["parent_attribute"],
					"child_attribute"=>$rowRel["child_attribute"]
					);
				}
				//retrieve info about child-entities
				$sSQL = "SELECT r.*, rk.parent_attribute, rk.child_attribute FROM relationship r " .
						"LEFT OUTER JOIN relationship_key rk " .
						"ON rk.parent_entity=r.parent_entity " .
						"AND rk.child_entity=r.child_entity " .
						"AND rk.number=r.number " .
						"WHERE r.child_entity ='$entity'";
				$objSDAO->query($sSQL,"selRel");
				while($rowRel = $objSDAO->fetch("selRel")){
					$numRel ++;
					$aExportEntity[$row["entity"]]["relationship"][$numRel] = array(
					"parent_entity"=>$rowRel["parent_entity"], 
					"child_entity"=>$rowRel["child_entity"], 
					"number"=>$rowRel["number"], 
					"description_parent_child"=>$rowRel["description_parent_child"], 
					"description_child_parent"=>$rowRel["description_child_parent"], 
					"note"=>$rowRel["note"], 
					"parent_optionality"=>$rowRel["parent_optionality"], 
					"children_optionality"=>$rowRel["children_optionality"], 
					"children_multeplicity"=>$rowRel["children_multeplicity"], 
					"index_creation"=>$rowRel["index_creation"], 
					"integrity_check"=>$rowRel["integrity_check"],
					"parent_attribute"=>$rowRel["parent_attribute"],
					"child_attribute"=>$rowRel["child_attribute"]
					);
				}
				//retrieve info about subtyped-entities
				$sSQL = "SELECT s.* FROM subtyping s
						WHERE s.supertype_entity ='$entity'";
				$objSDAO->query($sSQL,"selSub");
				$numSub = 0;
				while($rowSub = $objSDAO->fetch("selSub")){
					$numSub ++;
					$aExportEntity[$row["entity"]]["subtyping"][$numSub] = array(
					"supertype_entity"=>$rowSub["supertype_entity"], 
					"description"=>$rowSub["description"], 
					"note"=>$rowSub["note"], 
					"subtype_entity"=>$rowSub["subtype_entity"], 
					"discriminating_attribute"=>$rowSub["discriminating_attribute"], 
					"discriminating_value"=>$rowSub["discriminating_value"]
					);
				}
				//retrieve info about subtyping-entities
				$sSQL = "SELECT s.description, s.note, sc.* FROM subtyping s
							LEFT OUTER JOIN subtyping_code sc
							ON sc.supertype_entity=s.supertype_entity
							AND sc.discriminating_attribute=s.discriminating_attribute 
							WHERE sc.subtype_entity ='$entity'";
				$objSDAO->query($sSQL,"selSub1");
				while($rowSub = $objSDAO->fetch("selSub1")){
					$aExportEntity[$row["entity"]]["subtyping"][$numSub] = array(
					"supertype_entity"=>$rowSub["supertype_entity"], 
					"description"=>$rowSub["description"], 
					"note"=>$rowSub["note"], 
					"subtype_entity"=>$rowSub["subtype_entity"], 
					"discriminating_attribute"=>$rowSub["discriminating_attribute"], 
					"discriminating_value"=>$rowSub["discriminating_value"]
					);
				}
			}
		}
		$sExport = serialize($aExportEntity);
		fwrite($fh, $sExport);
		fclose($fh);
		$errMsg .= "Esportreqtype terminata con successo.<br><a target=\"_blank\" href=\"".SERVER_URL_ROOT."/entity_export.txt\"><b>Scarica il file</b></a><br>";
	}	
	return $errMsg;
}

function entityImp(){
	global $objSDAO;
	$errMsg = "";
	$sLog = "";
	if(file_exists($_FILES["import_file"]["tmp_name"]) || $_FILES["import_file"]["size"] != 0){
		$sLog .= date("d/m/Y h:i:s")."\tinizio import\n";
		$sImport = file_get_contents($_FILES["import_file"]["tmp_name"]);
		$aEntities = unserialize($sImport);
		dbg_(print_r($aEntities, 1));
		if(count($aEntities) != 0){
			//Verifico che l'area dati esista, inserisco tutte le entity con i propri attributi e domini, 
			//poi le relazioni e i subtyping
			foreach($aEntities as $entity=>$aEntity){
				$data_area = $aEntity["data_area"];
				$sSQL = "SELECT count(*) AS num FROM data_area WHERE data_area='".$data_area."'";
				$objSDAO->query($sSQL, "test");
				$row = $objSDAO->fetch("test");
				if($row && $row["num"] != 0){
					//inizio l'importreqtype
					//inserisco l'entity se non esiste
					dbg_("Area dati esistente:<br>inserisco la entity ".$entity."<br>");
					$sLog .= date("d/m/Y h:i:s")."\tinserisco la entity ".$entity."\n";
					$sSQL = "SELECT count(*) AS num FROM entity WHERE entity='".$entity."'";
					$objSDAO->query($sSQL, "test2");
					$rowTest = $objSDAO->fetch("test2");
					if($rowTest && $rowTest["num"] != 0){
						$_REQUEST["ent"] = $entity;
						$errMsg .= entDelete(false);
						$sLog .= date("d/m/Y h:i:s")."\telimino l'entity $entity\n";
					}
					if($errMsg == ""){
						$sSQL = "INSERT INTO entity " .
								"(data_area, entity, description, note, physical_param) " .
								"VALUES " .
								"('".$aEntity["data_area"]."', '".$entity."', '".elabStmt($aEntity["description"])."', '".elabStmt($aEntity["note"])."', '".elabStmt($aEntity["physical_param"])."')";
						$objSDAO->query($sSQL, "insEnt");
						if($objSDAO->error == ""){
							//collego e/o inserisco le eventuali subject area
							if(isset($aEntity["subject_area"])){
								$aSubjAreas = $aEntity["subject_area"];
								foreach($aSubjAreas as $num=>$aSubjArea){
									$subject_area = $aSubjArea["subject_area"];
									$sSQL = "SELECT count(*) AS num FROM subject_area WHERE data_area='$data_area' AND subject_area='$subject_area'";
									$objSDAO->query($sSQL, "test12");
									$rowTest = $objSDAO->fetch("test12");
									if($rowTest && $rowTest["num"] == 0){
										//la subject_area non esiste e va inserita
										$sLog .= date("d/m/Y h:i:s")."\tinserisco la subject area $subject_area\n";
										$sSQL = "INSERT INTO subject_area " .
											"(data_area, subject_area, description, note) " .
											"VALUES " .
											"('$data_area', '$subject_area', '".elabStmt($aSubjArea["description"])."', '".elabStmt($aSubjArea["note"])."')";
										$objSDAO->query($sSQL, "insEntSA");
										if($objSDAO->error != ""){
											$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
										}
									}
									$sLog .= date("d/m/Y h:i:s")."\tcollego l'entity $entity alla subject area $subject_area\n";
									$sSQL = "INSERT INTO subject_area_entity " .
											"(data_area, subject_area, entity) " .
											"VALUES " .
											"('$data_area', '$subject_area', '$entity')";
									$objSDAO->query($sSQL, "insEntSA");
									if($objSDAO->error != ""){
										$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
									}
								}
							}
							//inserisco gli attributi
							if(isset($aEntity["attribute"])){
								$aAttributes = $aEntity["attribute"];
								foreach($aAttributes as $num=>$aAttribute){
									dbg_("inserisco l'attribute ".$aAttribute["attribute"]."<br>");
									$sLog .= date("d/m/Y h:i:s")."\tinserisco l'attribute ".$aAttribute["attribute"]."\n";
						
									$sSQL = "INSERT INTO attribute " .
											"(entity, attribute, sequence, domain, upgrade_status, length, decimals, " .
											"default_value, description, note, primary_key, indexed, " .
											"physical_param) " .
											"VALUES " .
											"('".$aAttribute["entity"]."', '".$aAttribute["attribute"]."', '".$aAttribute["sequence"]."', '".$aAttribute["domain"]."', '".elabStmt($aAttribute["upgrade_status"])."', " .
											"'".$aAttribute["length"]."', '".$aAttribute["decimals"]."', '".elabStmt($aAttribute["default_value"])."', '".elabStmt($aAttribute["description"])."', '".elabStmt($aAttribute["note"])."', " .
											"'".$aAttribute["primary_key"]."', '".$aAttribute["indexed"]."', '".elabStmt($aAttribute["physical_param"])."')";
									$objSDAO->query($sSQL, "insAtt");
									if($objSDAO->error != ""){
										$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
									}
								}
							}
						}else{
							$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
						}					
					}else{
						$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
					}
				}else{
					$errMsg .= "L'area dati ".$aEntity["data_area"]." &egrave; inesistente<br>";
					$sLog .= date("d/m/Y h:i:s")."\tarea dati $data_area inesistente\n";
					break;
				}
				//inserisco i domini se non esistono gi@
				if(isset($aEntity["domain"])){
					$aDomains = $aEntity["domain"];
					foreach($aDomains as $numD=>$aDomain){
						$sSQL = "SELECT count(*)AS num FROM domain WHERE domain='".$aDomain["domain"]."'";
						$objSDAO->query($sSQL, "test3");
						$rowTest = $objSDAO->fetch("test3");
						if($rowTest && $rowTest["num"] == 0){
							dbg_("inserisco il dominio ".$aDomain["domain"]."<br>");
							$sLog .= date("d/m/Y h:i:s")."\tinserisco il dominio ".$aDomain["domain"]."\n";
							$sSQL = "INSERT INTO domain " .
									"(domain, lookup_entity, lookup_attribute, upgrade_status, type, length, decimals, enumerable, description, note) " .
									"VALUES " .
									"('".$aDomain["domain"]."', '".$aDomain["lookup_entity"]."', '".$aDomain["lookup_attribute"]."', '".elabStmt($aDomain["upgrade_status"])."', '".$aDomain["type"]."', " .
									"'".$aDomain["length"]."', '".$aDomain["decimals"]."', '".$aDomain["enumerable"]."', '".elabStmt($aDomain["descr_dom"])."', '".elabStmt($aDomain["note_dom"])."')";
							$objSDAO->query($sSQL, "insDom");
							if($objSDAO->error != "")
								$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
							$value = isset($aDomain["value"])?$aDomain["value"]:"";
							$descr = isset($aDomain["descr_value"])?$aDomain["descr_value"]:"";
							$note = isset($aDomain["note_descr"])?$aDomain["note_descr"]:"";
							$sSQL = "INSERT INTO domain_value " .
									"(domain, value, description, note) " .
									"VALUES " .
									"('".$aDomain["domain"]."', '".$value."', '".elabStmt($descr)."', '".elabStmt($note)."')";
							$objSDAO->query($sSQL, "insDomVal");
							if($objSDAO->error != "")
								$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
						}
					}
				}
			}
			//inserisco relazioni e sottotipizzazioni
			//verificando che entrambe le entity coinvolte siano presenti
			foreach($aEntities as $entity=>$aEntity){
				if(isset($aEntity["relationship"])){
					$aRelationships = $aEntity["relationship"];
					foreach($aRelationships as $numR=>$aRelationship){
						$sSQL = "SELECT count(*)AS num FROM entity WHERE entity='".$aRelationship["parent_entity"]."' OR entity='".$aRelationship["child_entity"]."'";
						$objSDAO->query($sSQL, "test3");
						$rowTest = $objSDAO->fetch("test3");
						if($rowTest && $rowTest["num"] == 2){
							//verifico che la relreqtype non esista gi@
							$sSQL = "SELECT count(*)AS num FROM relationship " .
									"WHERE parent_entity='".$aRelationship["parent_entity"]."' " .
											"AND child_entity='".$aRelationship["child_entity"]."' " .
													"AND number=".$aRelationship["number"]."";
							$objSDAO->query($sSQL, "test4");
							$rowTest = $objSDAO->fetch("test4");
							if($rowTest && $rowTest["num"] == 0){	
								dbg_("inserisco la relreqtype ".$aRelationship["number"]." - ".$aRelationship["parent_entity"]." - ".$aRelationship["child_entity"]."<br>");
								$sLog .= date("d/m/Y h:i:s")."\tinserisco la relreqtype ".$aRelationship["number"]." - ".$aRelationship["parent_entity"]." - ".$aRelationship["child_entity"]."\n";
								$sSQL = "INSERT INTO relationship " .
										"(parent_entity, child_entity, number, description_parent_child, description_child_parent, note, parent_optionality, children_optionality, children_multeplicity, index_creation, integrity_check) " .
										"VALUES " .
										"('".$aRelationship["parent_entity"]."', '".$aRelationship["child_entity"]."', '".$aRelationship["number"]."', '".elabStmt($aRelationship["description_parent_child"])."', '".elabStmt($aRelationship["description_child_parent"])."', " .
										"'".elabStmt($aRelationship["note"])."', '".$aRelationship["parent_optionality"]."', '".$aRelationship["children_optionality"]."', '".$aRelationship["children_multeplicity"]."', '".$aRelationship["index_creation"]."', '".$aRelationship["integrity_check"]."')";
								$objSDAO->query($sSQL, "insRel");
								if($objSDAO->error != "")
									$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
							}	
							//verifico che la chiave non esista gi@
							$sSQL = "SELECT count(*)AS num FROM relationship_key " .
									"WHERE parent_entity='".$aRelationship["parent_entity"]."' " .
											"AND child_entity='".$aRelationship["child_entity"]."' " .
											"AND parent_attribute='".$aRelationship["parent_attribute"]."' " .
											"AND child_attribute='".$aRelationship["child_attribute"]."' " .
											"AND number=".$aRelationship["number"]."";
							$objSDAO->query($sSQL, "test5");
							$rowTest = $objSDAO->fetch("test5");
							if($rowTest && $rowTest["num"] == 0){					
								dbg_("inserisco la chiave della relreqtype ".$aRelationship["number"]." - ".$aRelationship["parent_entity"]." - ".$aRelationship["child_entity"]."<br>");
								$sLog .= date("d/m/Y h:i:s")."\tinserisco la chiave della relreqtype ".$aRelationship["number"]." - ".$aRelationship["parent_entity"]." - ".$aRelationship["child_entity"]."\n";
								$sSQL = "INSERT INTO relationship_key " .
										"(parent_entity, child_entity, number, parent_attribute, child_attribute) " .
										"VALUES " .
										"('".$aRelationship["parent_entity"]."', '".$aRelationship["child_entity"]."', '".$aRelationship["number"]."', '".$aRelationship["parent_attribute"]."', '".$aRelationship["child_attribute"]."')";
								$objSDAO->query($sSQL, "insRelKey");
								if($objSDAO->error != "")
									$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
							}
				
						}
					}
				}
				if(isset($aEntity["subtyping"])){
					$aSubTypings = $aEntity["subtyping"];
					foreach($aSubTypings as $numR=>$aSubTyping){
						$sSQL = "SELECT count(*)AS num FROM entity WHERE entity='".$aSubTyping["supertype_entity"]."' OR entity='".$aSubTyping["subtype_entity"]."'";
						$objSDAO->query($sSQL, "test3");
						$rowTest = $objSDAO->fetch("test3");
						if($rowTest && $rowTest["num"] == 2){
							//verifico che la relreqtype non esista gi@
							$sSQL = "SELECT count(*)AS num FROM subtyping " .
									"WHERE supertype_entity='".$aSubTyping["supertype_entity"]."'";
							$objSDAO->query($sSQL, "test4");
							$rowTest = $objSDAO->fetch("test4");
							if($rowTest && $rowTest["num"] == 0){	
								dbg_("inserisco la sottotipizzreqtype ".$aSubTyping["supertype_entity"]."<br>");
								$sLog .= date("d/m/Y h:i:s")."\tinserisco la sottotipizzreqtype ".$aSubTyping["supertype_entity"]."\n";
								$sSQL = "INSERT INTO subtyping " .
										"(supertype_entity, description, note, discriminating_attribute) " .
										"VALUES " .
										"('".$aSubTyping["supertype_entity"]."', '".elabStmt($aSubTyping["description"])."', " .
												"'".elabStmt($aSubTyping["note"])."', '".$aSubTyping["discriminating_attribute"]."')";
								$objSDAO->query($sSQL, "insSub");
								if($objSDAO->error != "")
									$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
							}	
							//verifico che il valore non esista gi@
							$sSQL = "SELECT count(*)AS num FROM subtyping_code " .
									"WHERE supertype_entity='".$aSubTyping["supertype_entity"]."' " .
									"AND discriminating_attribute='".$aSubTyping["discriminating_attribute"]."' " .
									"AND discriminating_value='".$aSubTyping["discriminating_value"]."'";
							$objSDAO->query($sSQL, "test5");
							$rowTest = $objSDAO->fetch("test5");
							if($rowTest && $rowTest["num"] == 0){					
								dbg_("inserisco il valore di subtyping ".$aSubTyping["discriminating_value"]."<br>");
								$sLog .= date("d/m/Y h:i:s")."\tinserisco il valore di subtyping ".$aSubTyping["discriminating_value"]."\n";
								$sSQL = "INSERT INTO subtyping_code " .
										"(supertype_entity, discriminating_attribute, discriminating_value, subtype_entity) " .
										"VALUES " .
										"('".$aSubTyping["supertype_entity"]."', '".$aSubTyping["discriminating_attribute"]."', " .
												"'".$aSubTyping["discriminating_value"]."', '".$aSubTyping["subtype_entity"]."')";
								$objSDAO->query($sSQL, "insSubVal");
								if($objSDAO->error != "")
									$errMsg .= "ERRORE SQL: ".$objSDAO->error."<br><br>$sSQL<br><br>";
							}
				
						}
					}		
				}		
			}
			
		}else{
			$errMsg .= "nessuna entity da importare<br>";
		}
	}else{
		$errMsg .= "caricare un file valido<br>";
	}
	$fh = fopen(SERVER_FS_ROOT."/import_log.txt", "wb");
	fwrite($fh, $sLog);
	fclose($fh);
	if($errMsg == "")
		$errMsg .= "Importreqtype terminata con successo<br>";
	$errMsg .= "<br><a target=\"_BLANK\" href=\"".SERVER_URL_ROOT."/import_log.txt\">LOG</a>";
	return $errMsg;
}

function elabStmt($string){
	return str_replace("'", "''", $string);
}

